package com.example.fund.fundstocks;

import java.io.BufferedReader;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.HttpURLConnection;
import java.net.URL;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import jxl.Sheet;
import jxl.Workbook;

import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import org.json.JSONArray;
import org.json.JSONObject;

import com.example.fund.Constant;
import com.example.fund.amplitude.Amplitude;
import com.example.fund.amplitude.IAmplitude;
import com.example.fund.info.FundInfo;
import com.example.fund.updatestategy.FundNetValueUpdateStrategy;
import com.example.fund.updatestategy.UpdateStrategy;
import com.example.fund.util.AssetsUtil;
import com.example.fund.util.DBManager;
import com.example.fund.util.FundUtil;
import com.example.fund.util.FundUtil1;
import com.example.fund.util.StringUtil;

import android.app.Activity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;

public class FundStocksUtil {
	private final static DateFormat DF1 = new SimpleDateFormat("yyyy-MM-dd");
	
	private static String url1 = "http://fund.eastmoney.com/f10/FundArchivesDatas.aspx?type=jjcc&code=#code&topline=10&year=#year";
	//中证行业分类架构
	private static String url2 = "http://www.csindex.com.cn/sseportal/ps/zhs/hqjt/csi/ZzhyflJg.xls";
	//中证行业分类表
	private static String url3 = "http://www.csindex.com.cn/sseportal/ps/zhs/hqjt/csi/ZzhyflWz.xls";
	private static String fileName3 = "ZzhyflWz.xls";
	
	private static String url4 = "http://q.10jqka.com.cn/zs/detail/field/199112/order/desc/page/#page/ajax/1/code/#code";
	private static String url5 = "http://hqquery.jrj.com.cn/idxsharesq.do?size=50&vname=elementsHqData&ids=#code&order=desc&sort=impact&page=#page";
	private static String url6 = "http://app.finance.china.com.cn/stock/data/sample_info.php?symbol=#code";
	
	/**
	 * 加载基金十大持仓
	 * @param
	 * @param code
	 */
	public static void loadFundStocks(String code)
	{
		HttpURLConnection con = null;
		BufferedReader reader = null;
		String s = null;
		
		StringBuffer sb = new StringBuffer();
		List<String> dateList = new LinkedList<String>();
		try
		{
			String year = "";
			int j=0;
			for(int i=0;i<4&&j<12;i++)
			{
				String urlStr = url1.replace("#code", code).replace("#year", year);
				URL url = new URL(urlStr);
				con = (HttpURLConnection) url.openConnection();
				con.setRequestMethod("GET");
				reader = new BufferedReader(new InputStreamReader(con.getInputStream(),"UTF-8"));
				s = reader.readLine().replaceAll("&nbsp;", "");
//			System.out.println(s); 
				//截取日期
				Pattern pattern = Pattern.compile("[0-9]{4}[-][0-9]{1,2}[-][0-9]{1,2}");
				Matcher matcher = pattern.matcher(s);
				while(matcher.find()){  
					String dateStr = matcher.group(0);
					dateList.add(dateStr);
					year = (Integer.valueOf(dateStr.substring(0,dateStr.indexOf('-')))-1)+"";
				} 
				
				if(s.indexOf("<tbody>")<0 || s.indexOf("</tbody>")<0)
				{
					break;
				}
				while(s.indexOf("<tbody>")>=0 && s.indexOf("</tbody>")>=0 && j<12)
				{
					String ts = s.substring(s.indexOf("<tbody>"),s.indexOf("</tbody>")+8);
					sb.append(ts);
					s = s.substring(s.indexOf("</tbody>")+8);
					j++;
				}
				
//			System.out.println(s); 
			}

		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			if(reader!=null)
			{
				try {
					reader.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			if(con!=null)
			{
				con.disconnect();
			}
		}
		if(sb.length()==0)
		{
			return;
		}
		
		//2.解析字符串，并计算成累积净值的形式保存

		Document xmlDoc = null;
		try
		{
			s = "<root>"+sb+"</root>";
			InputStream inputStream = new ByteArrayInputStream(s.getBytes("UTF-8"));
			SAXReader sax = new SAXReader();
			xmlDoc = sax.read(inputStream);
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		if(xmlDoc==null)
		{
			return;
		}
		Element root = xmlDoc.getRootElement();//根节点
		List tbodys = root.elements("tbody");
		if(tbodys==null || tbodys.size()==0)
		{
			return;
		}
		
		String delSql = "delete from FundStocks where fundnumber=?";
//		String delSql = "delete from FundStocks";
        StringBuffer insertSql = new StringBuffer();
        insertSql.append("	insert into FundStocks (	\n");
        insertSql.append("	    id,	\n");
        insertSql.append("	    fundnumber,	\n");
        insertSql.append("	    date,	\n");
        insertSql.append("	    stocksnumber,	\n");
        insertSql.append("	    stocksname,	\n");
        insertSql.append("	    share	\n");
        insertSql.append("	) values (	\n");
        insertSql.append("	    null,	\n");
        insertSql.append("	    ?,	\n");
        insertSql.append("	    ?,	\n");
        insertSql.append("	    ?,	\n");
        insertSql.append("	    ?,	\n");
        insertSql.append("	    ?	\n");
        insertSql.append("	)   	\n");
        
		SQLiteDatabase db = DBManager.getDB();
		db.beginTransaction();
		try {
			SQLiteStatement stmt = db.compileStatement(insertSql.toString());
			
			SQLiteStatement stmt1 = db.compileStatement(delSql);
			stmt1.bindString(1, code);
		    stmt1.execute();  
		    stmt1.clearBindings(); 
		    
		    for(int j=0;j<tbodys.size();j++)
		    {
		    	if(dateList.size()<=j)
		    	{
		    		continue;
		    	}
		    	String dateStr = dateList.get(j);
		    	System.out.println(code+dateStr);
		    	Element tbody = (Element)tbodys.get(j);
		    	List trs = tbody.elements("tr");
		    	for(int i=0;i<trs.size();i++)
		    	{
		    		Element tr = (Element)trs.get(i);
		    		List tds = tr.elements("td");
		    		if(tds.size()<7)
		    		{
		    			continue;
		    		}
		    		//编码列
		    		List as = ((Element)tds.get(1)).elements("a");
		    		if(as==null || as.size()==0)
		    		{
		    			continue;
		    		}
		    		String number = ((Element)as.get(0)).getTextTrim();
		    		List as1 = ((Element)tds.get(2)).elements("a");
		    		if(as1==null || as1.size()==0)
		    		{
		    			continue;
		    		}
		    		String name = ((Element)as1.get(0)).getTextTrim();
		    		String share = ((Element)tds.get(j==0?6:4)).getTextTrim();
		    		if(number==null || name==null || share==null
		    				|| number.trim().length()==0 || name.trim().length()==0 || share.trim().length()==0)
		    		{
		    			continue;
		    		}
		    		
	//	    		System.out.println(number+name+share);
		    		
		    		stmt.bindString(1, code);
		    		stmt.bindString(2, dateStr);
		    		stmt.bindString(3, number);  
		    		stmt.bindString(4, name);
		    		double shareTemp = 0;
		    		try {
		    			shareTemp = Double.valueOf(share.replace("%", ""));
					} catch (Exception e) {
						e.printStackTrace();
					}
		    		stmt.bindDouble(5, shareTemp);  
		    		stmt.execute();  
		    		stmt.clearBindings(); 
		    	}
		    }
			db.setTransactionSuccessful(); 
		} catch (Exception e) {
			e.printStackTrace();
		} finally
		{
			db.endTransaction();
		}
	}
	
	/**
	 * 查询要更新的基金编号集合
	 * @return
	 */
	public static List<String> getNeedUpdateFundNumber()
	{
		List<String> list = new LinkedList<String>();
		
		int n = 12;//取n季度的数据
		String lastestQuarter = getLatestQuarterDateList(n).get(0);
		
		StringBuffer sql = new StringBuffer();
		sql.append("	select 	\n");
		sql.append("	    f.number	\n");
		sql.append("	from fund f 	\n");
		sql.append("	left join FundStocks fs on f.number=fs.fundnumber and fs.date ='"+lastestQuarter+"'	\n");
//		sql.append("	where f.number='001475'	\n");
		sql.append("	where (f.type like '%股票%' or f.type like '%混合%' or f.type like '%创新%' or f.type like '%指数%'  or f.type like '%分级%' or f.type like '%ETF%' or f.type like '%债券%' or f.type like '%联接%' or f.type like '%保本%')	\n");
		sql.append("	group by f.number	\n");
		sql.append("	having count(fs.id)=0	\n");
		
		SQLiteDatabase db = DBManager.getDB();

		Cursor c = db.rawQuery(sql.toString(), null);
		while (c.moveToNext())
		{
            String number = c.getString(c.getColumnIndex("number"));
            
            list.add(number);
		}
		c.close();
		
		return list;
	}
	
	/**
	 * 加载中证行业分类架构
	 * @param
	 */
	public static boolean loadZZHYFL()
	{
		SQLiteDatabase db = DBManager.getDB();

		HttpURLConnection con = null;
		BufferedReader reader = null;
		List<String> list = new ArrayList<String>();
		try {
			URL url = new URL(url2);
			con = (HttpURLConnection) url.openConnection();
			con.setRequestMethod("GET");

			InputStream is = con.getInputStream();
		    ByteArrayOutputStream output = new ByteArrayOutputStream();
		    byte[] buffer = new byte[1024];
		    int n = 0;
		    while (-1 != (n = is.read(buffer))) {
		        output.write(buffer, 0, n);
		    }
			InputStream inputStream = new ByteArrayInputStream(output.toByteArray());
			Workbook book = Workbook.getWorkbook(inputStream);

			// 获得第一个工作表对象
			Sheet sheet = book.getSheet(0);
			int rows = sheet.getRows();
			int cols = sheet.getColumns();
			if(cols<8)
			{
				return false;
			}
			for(int i=0;i<=6;i=i+2)
			{
				for(int j=0;j<rows;j++)
				{
					String number = sheet.getCell(i,j).getContents();
					String name = sheet.getCell(i+1,j).getContents();
					if(number==null || number.trim().length()==0
							|| name==null || name.trim().length()==0)
					{
						continue;
					}
					String value = number+"|"+name;
					if(!list.contains(value))
					{
						list.add(value);
					}
				}
			}
			book.close();
		} catch (Exception e) {
			System.out.println(e);
			return false;
		} finally
		{
			if(reader!=null)
			{
				try {
					reader.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			if(con!=null)
			{
				con.disconnect();
			}
		}
		
		if(list.size()==0)
		{
			return false;
		}
		
		String delSql = "delete from zzhyfl";
        StringBuffer insertSql = new StringBuffer();
        insertSql.append("	insert into zzhyfl (	\n");
        insertSql.append("	    id,	\n");
        insertSql.append("	    number,	\n");
        insertSql.append("	    name	\n");
        insertSql.append("	) values (	\n");
        insertSql.append("	    null,	\n");
        insertSql.append("	    ?,	\n");
        insertSql.append("	    ?	\n");
        insertSql.append("	)   	\n");
		
		db.beginTransaction();
		SQLiteStatement stmt = db.compileStatement(insertSql.toString());
		SQLiteStatement stmt1 = db.compileStatement(delSql);
		stmt1.execute();
		
		for(int i=0;i<list.size();i++)
		{
			String[] strArr = list.get(i).split("\\|");
			if(strArr.length<2)
			{
				continue;
			}
		    stmt.bindString(1, strArr[0]);  
		    stmt.bindString(2, strArr[1]);  
		    stmt.execute();  
		    stmt.clearBindings(); 
		}
		
		db.setTransactionSuccessful();  
		db.endTransaction(); 
		
		return true;
	}
	
	/**
	 * 加载股票分类
	 * @param
	 */
	public static void loadStocksFLFromLocalhost(Activity activity)
	{
		SQLiteDatabase db = DBManager.getDB();

		List<String> list = new ArrayList<String>();
		try {
			InputStream inputStream = activity.getResources().getAssets().open(fileName3);
			Workbook book = Workbook.getWorkbook(inputStream);

			// 获得第一个工作表对象
			Sheet sheet = book.getSheet(0);
			int rows = sheet.getRows();
			int cols = sheet.getColumns();
			if(cols<14)
			{
				return;
			}
			for(int i=1;i<rows;i=i+1)
			{
				String number = sheet.getCell(0,i).getContents();
				String name = sheet.getCell(1,i).getContents();
				String level1 = sheet.getCell(4,i).getContents();
				String level2 = sheet.getCell(7,i).getContents();
				String level3 = sheet.getCell(10,i).getContents();
				String level4 = sheet.getCell(13,i).getContents();
				list.add(number+"|"+name+"|"+level1+"|"+level2+"|"+level3+"|"+level4);
				System.out.println(number+"|"+name+"|"+level1+"|"+level2+"|"+level3+"|"+level4);
			}
			book.close();
		} catch (Exception e) {
			System.out.println(e);
		} finally
		{
		}
		
		if(list.size()==0)
		{
			return;
		}
		
		String delSql = "delete from Stocksfl";
        StringBuffer insertSql = new StringBuffer();
        insertSql.append("	insert into Stocksfl (	\n");
        insertSql.append("	    id,	\n");
        insertSql.append("	    number,	\n");
        insertSql.append("	    name,	\n");
        insertSql.append("	    level1,	\n");
        insertSql.append("	    level2,	\n");
        insertSql.append("	    level3,	\n");
        insertSql.append("	    level4	\n");
        insertSql.append("	) values (	\n");
        insertSql.append("	    null,	\n");
        insertSql.append("	    ?,	\n");
        insertSql.append("	    ?,	\n");
        insertSql.append("	    ?,	\n");
        insertSql.append("	    ?,	\n");
        insertSql.append("	    ?,	\n");
        insertSql.append("	    ?	\n");
        insertSql.append("	)   	\n");
		
		db.beginTransaction();
		SQLiteStatement stmt = db.compileStatement(insertSql.toString());
		SQLiteStatement stmt1 = db.compileStatement(delSql);
		stmt1.execute();
		
		for(int i=0;i<list.size();i++)
		{
			String[] strArr = list.get(i).split("\\|");
			if(strArr.length<6)
			{
				continue;
			}
		    stmt.bindString(1, strArr[0]);  
		    stmt.bindString(2, strArr[1]);  
		    stmt.bindString(3, strArr[2]);  
		    stmt.bindString(4, strArr[3]);  
		    stmt.bindString(5, strArr[4]);  
		    stmt.bindString(6, strArr[5]);  
		    stmt.execute();  
		    stmt.clearBindings(); 
		}
		
		db.setTransactionSuccessful();  
		db.endTransaction(); 
	}
	
	/**
	 * 加载股票分类
	 * @param
	 */
	public static boolean loadStocksFL()
	{
		HttpURLConnection con = null;
		BufferedReader reader = null;
		List<String> list = new ArrayList<String>();
		try {
			URL url = new URL(url3);
			con = (HttpURLConnection) url.openConnection();
			con.setRequestMethod("GET");

			InputStream is = con.getInputStream();
		//	InputStream is = new FileInputStream(url3); 
		    ByteArrayOutputStream output = new ByteArrayOutputStream();
		    byte[] buffer = new byte[10];
		    int n = 0;
		    while (-1 != (n = is.read(buffer))) {
		        output.write(buffer, 0, n);
		    }
			InputStream inputStream = new ByteArrayInputStream(output.toByteArray());
			Workbook book = Workbook.getWorkbook(inputStream);

			// 获得第一个工作表对象
			Sheet sheet = book.getSheet(0);
			int rows = sheet.getRows();
			int cols = sheet.getColumns();
			if(cols<14)
			{
				return false;
			}
			for(int i=1;i<rows;i=i+1)
			{
				String number = sheet.getCell(0,i).getContents();
				String name = sheet.getCell(1,i).getContents();
				String level1 = sheet.getCell(4,i).getContents();
				String level2 = sheet.getCell(7,i).getContents();
				String level3 = sheet.getCell(10,i).getContents();
				String level4 = sheet.getCell(13,i).getContents();
				list.add(number+"|"+name+"|"+level1+"|"+level2+"|"+level3+"|"+level4);
	//			System.out.println(number+"|"+name+"|"+level1+"|"+level2+"|"+level3+"|"+level4);
			}
			book.close();
		} catch (Exception e) {
			System.out.println(e);
			return false;
		} finally
		{
			if(reader!=null)
			{
				try {
					reader.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			if(con!=null)
			{
				con.disconnect();
			}
		}
		
		if(list.size()==0)
		{
			return false;
		}
		
		String delSql = "delete from Stocksfl";
        StringBuffer insertSql = new StringBuffer();
        insertSql.append("	insert into Stocksfl (	\n");
        insertSql.append("	    id,	\n");
        insertSql.append("	    number,	\n");
        insertSql.append("	    name,	\n");
        insertSql.append("	    level1,	\n");
        insertSql.append("	    level2,	\n");
        insertSql.append("	    level3,	\n");
        insertSql.append("	    level4	\n");
        insertSql.append("	) values (	\n");
        insertSql.append("	    null,	\n");
        insertSql.append("	    ?,	\n");
        insertSql.append("	    ?,	\n");
        insertSql.append("	    ?,	\n");
        insertSql.append("	    ?,	\n");
        insertSql.append("	    ?,	\n");
        insertSql.append("	    ?	\n");
        insertSql.append("	)   	\n");
		
		SQLiteDatabase db = DBManager.getDB();

		try {
			db.beginTransaction();
			SQLiteStatement stmt = db.compileStatement(insertSql.toString());
			SQLiteStatement stmt1 = db.compileStatement(delSql);
			stmt1.execute();
			
			for(int i=0;i<list.size();i++)
			{
				String[] strArr = list.get(i).split("\\|");
				if(strArr.length<6)
				{
					continue;
				}
			    stmt.bindString(1, strArr[0]);  
			    stmt.bindString(2, strArr[1]);  
			    stmt.bindString(3, strArr[2]);  
			    stmt.bindString(4, strArr[3]);  
			    stmt.bindString(5, strArr[4]);  
			    stmt.bindString(6, strArr[5]);  
			    stmt.execute();  
			    stmt.clearBindings(); 
			}
			
			db.setTransactionSuccessful(); 
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally
		{
			db.endTransaction();
		}
		
		return true;
	}
	
	/**
	 * 加载指数成分股
	 * @param number
	 * @return
	 */
	public static boolean loadIndexStocks(String number)
	{
		SQLiteDatabase db = DBManager.getDB();
		
		String code1 = null;
		if(number.equals("0000001"))
		{
			code1 = "1A0001";
		}else if(number.startsWith("000"))
		{
			code1 = "1B"+number.substring(3);
		}else
		{
			code1 = number.substring(1);
		}
		
		//1.从网络下载。然后截取成XML格式
		String dataURL = url4.replace("#code", code1);
		int totalPage = 1;
		List<String> indxStocksList = new LinkedList<String>();
		
		String delSql = "delete from indexStocks where indexnumber='"+number+"'";
		String insertSql = "insert into indexStocks(id,stocksnumber,indexnumber) values (null,?,'"+number+"')";
		SQLiteStatement stmt1 = db.compileStatement(delSql);
		SQLiteStatement stmt = db.compileStatement(insertSql);
		
		for(int i=1;i<=totalPage;i++)
		{
			String adataURL = dataURL.replace("#page", i+"");
			HttpURLConnection con = null;
			BufferedReader reader = null;
			
			try
			{
				URL url = new URL(adataURL);
				con = (HttpURLConnection) url.openConnection();
				con.setRequestMethod("GET");
				reader = new BufferedReader(new InputStreamReader(con.getInputStream(),"GBK"));
				boolean start = false;
				boolean end = false;
				StringBuffer sb = new StringBuffer();
				String s = null;
				while((s = reader.readLine())!=null)
				{
					if(s.contains("指数列表"))
					{
						continue;
					}
					if(s.contains("j_addStock"))
					{
						continue;
					}
					if(s.contains("<tbody>"))
					{
						start = true;
					}
					if(start)
					{
						if(!end)
						{
							sb.append(s.trim());
//							System.out.println(s);
						}
						if(s.contains("</tbody>"))
						{
							end = true;
						}
					}
			//		Pattern pattern = Pattern.compile("共(.*)页&");
					Pattern pattern = Pattern.compile("<span class=\"page_info\">(.*)</span>");
					Matcher matcher = pattern.matcher(s);
					if(matcher.find())
					{
						String pageNum = matcher.group(1).trim();
						totalPage = Integer.valueOf(pageNum.substring(pageNum.indexOf('/')+1));
					}
				}
				if(sb.length()==0)
				{
					break;
				}
				
				//2.解析字符串，并计算成累积净值的形式保存

				Document xmlDoc = null;
				try
				{
					InputStream inputStream = new ByteArrayInputStream(sb.toString().getBytes("UTF-8"));
					SAXReader sax = new SAXReader();
					xmlDoc = sax.read(inputStream);
				}
				catch (DocumentException e)
				{
					e.printStackTrace();
				}
				catch (UnsupportedEncodingException e)
				{
					e.printStackTrace();
				}
				if(xmlDoc==null)
				{
					break;
				}
				
				Element tbody = xmlDoc.getRootElement();//根节点
				List trs = tbody.elements("tr");
				for(int j=0;j<trs.size();j++)
				{
					Element tr = (Element)trs.get(j);
					List tds = tr.elements("td");
					if(tds.size()<3)
					{
						continue;
					}
					String stocksNumber = ((Element)tds.get(1)).elementTextTrim("a");
					if(stocksNumber!=null && stocksNumber.length()>0)
					{
						indxStocksList.add(stocksNumber);
					}
				}
			}
			catch (Exception e)
			{
				e.printStackTrace();
			}
			finally
			{
				if(reader!=null)
				{
					try {
						reader.close();
					} catch (IOException e) {
						e.printStackTrace();
					}
				}
				if(con!=null)
				{
					con.disconnect();
				}
			}
		}

		db.beginTransaction();
		stmt1.execute();
		try {
			for(int i=indxStocksList.size()-1;i>=0;i--)
			{
				String stocksNumber = indxStocksList.get(i);
				stmt.bindString(1, stocksNumber);  
				stmt.execute();  
				stmt.clearBindings();
			}
			db.setTransactionSuccessful();
		} catch (Exception e){
			e.printStackTrace();
			return false;
		} finally
		{
			db.endTransaction();
		}
		
		return true;
	}
	/**
	 * 加载指数成分股
	 * @param number
	 * @return
	 */
	public static boolean loadIndexStocks1(String number)
	{
		SQLiteDatabase db = DBManager.getDB();
		
		String code1 = number.substring(1);;
		
		//1.从网络下载。然后截取成XML格式
		String dataURL = url5.replace("#code", code1);
		int totalPage = 200;
		List<String> indxStocksList = new LinkedList<String>();
		
		String delSql = "delete from indexStocks where indexnumber='"+number+"'";
		String insertSql = "insert into indexStocks(id,stocksnumber,indexnumber,startDate,endDate) values (null,?,'"+number+"',?,?)";
		SQLiteStatement stmt1 = db.compileStatement(delSql);
		SQLiteStatement stmt = db.compileStatement(insertSql);
		
		for(int i=1;i<=totalPage;i++)
		{
			String adataURL = dataURL.replace("#page", i+"");
			HttpURLConnection con = null;
			BufferedReader reader = null;
			
			try
			{
				URL url = new URL(adataURL);
				con = (HttpURLConnection) url.openConnection();
				con.setRequestMethod("GET");
				reader = new BufferedReader(new InputStreamReader(con.getInputStream(),"GBK"));
				StringBuffer sb = new StringBuffer();
				String s = null;
				while((s = reader.readLine())!=null)
				{
					sb.append(s);
				}
				String str = sb.toString();
				if(str.length()==0 || str.indexOf("=")==-1)
				{
					break;
				}
				
				//2.解析字符串
				str = str.substring(str.indexOf("=")+1);
				JSONObject json = new JSONObject(str);
				JSONArray jsonArr = json.getJSONArray("StockHq");
				if(jsonArr==null || jsonArr.length()==0)
				{
					break;
				}
				for(int j=0,k=jsonArr.length();j<k;j++)
				{
					JSONObject data = jsonArr.getJSONObject(j);
					String a3 = data.getString("A3");
					if(a3==null || a3.length()==0)
					{
						continue;
					}
					indxStocksList.add(a3);
				}
			}
			catch (Exception e)
			{
				e.printStackTrace();
			}
			finally
			{
				if(reader!=null)
				{
					try {
						reader.close();
					} catch (IOException e) {
						e.printStackTrace();
					}
				}
				if(con!=null)
				{
					con.disconnect();
				}
			}
		}
		
		db.beginTransaction();
		stmt1.execute();
		try {
			String startDate = "2010-01-06";
			for(int i=indxStocksList.size()-1;i>=0;i--)
			{
				String stocksNumber = indxStocksList.get(i);
				stmt.bindString(1, stocksNumber);
				stmt.bindString(2, startDate);
				stmt.bindString(3, "");
				stmt.execute();  
				stmt.clearBindings();
			}
			db.setTransactionSuccessful();
		} catch (Exception e){
			e.printStackTrace();
			return false;
		} finally
		{
			db.endTransaction();
		}
		
		return true;
	}
	
	/**
	 * 加载指数成分股
	 * @param number
	 * @return
	 */
	public static boolean loadIndexStocks2(String number)
	{
		SQLiteDatabase db = DBManager.getDB();
		
		String code1 = number.substring(1);
		
		String year = (Calendar.getInstance().get(Calendar.YEAR)-5)+"";
		
		//1.从网络下载。然后截取成XML格式
		String dataURL = url6.replace("#code", code1);
		
		String delSql = "delete from indexStocks where indexnumber='"+number+"'";
		String insertSql = "insert into indexStocks(id,stocksnumber,indexnumber,startDate,endDate) values (null,?,'"+number+"',?,?)";
		SQLiteStatement stmt1 = db.compileStatement(delSql);
		SQLiteStatement stmt = db.compileStatement(insertSql);
		
		HttpURLConnection con = null;
		BufferedReader reader = null;

		db.beginTransaction();
		stmt1.execute();
		try
		{
			URL url = new URL(dataURL);
			con = (HttpURLConnection) url.openConnection();
			con.setRequestMethod("GET");
			reader = new BufferedReader(new InputStreamReader(con.getInputStream(),"UTF-8"));
			boolean start = false;
			boolean end = false;
			StringBuffer sb = new StringBuffer();
			String s = null;
			int i=0;
			while((s = reader.readLine())!=null)
			{
				if(s.contains("<table"))
				{
					start = true;
				}
				if(start)
				{
						sb.append(s.trim());
						//							System.out.println(s);
					if(s.contains("</table>"))
					{
						break;
					}
				}
				if(i++>30000)
				{
					break;
				}
			}
			if(sb.length()==0)
			{
				return false;
			}

			//2.解析字符串，并计算成累积净值的形式保存

			Document xmlDoc = null;
			try
			{
				InputStream inputStream = new ByteArrayInputStream(sb.toString().getBytes("UTF-8"));
				SAXReader sax = new SAXReader();
				xmlDoc = sax.read(inputStream);
			}
			catch (DocumentException e)
			{
				e.printStackTrace();
			}
			catch (UnsupportedEncodingException e)
			{
				e.printStackTrace();
			}
			if(xmlDoc==null)
			{
				return false;
			}

			Element tbody = xmlDoc.getRootElement();//根节点
			List trs = tbody.elements("tr");
			for(int j=1;j<trs.size();j++)
			{
				Element tr = (Element)trs.get(j);
				List tds = tr.elements("td");
				if(tds.size()<3)
				{
					continue;
				}
				String stocksNumber = ((Element)tds.get(0)).elementTextTrim("a");
				String startDate = ((Element)tds.get(2)).getTextTrim();
				String endDate = ((Element)tds.get(3)).elementTextTrim("span");
				if(StringUtil.isNullOrEmpty(stocksNumber))
				{
					continue;
				}
				if(!StringUtil.isNullOrEmpty(endDate) && endDate.contains("0000"))
				{
					endDate = "";
				}
				stmt.bindString(1, stocksNumber);
				stmt.bindString(2, startDate);
				stmt.bindString(3, endDate);
				stmt.execute();
				stmt.clearBindings();
			}
			db.setTransactionSuccessful();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			if(reader!=null)
			{
				try {
					reader.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
			if(con!=null)
			{
				con.disconnect();
			}
			db.endTransaction();
		}
		
		return true;
	}
	
	/**
	 * 创建数据表
	 * @param
	 */
	public static void createTable()
	{
/*        StringBuffer sql = new StringBuffer();
        sql.append("	create table FundStocks (	\n");
        sql.append("	id INTEGER PRIMARY KEY AUTOINCREMENT,	\n");
        sql.append("	fundnumber TEXT,	\n");
        sql.append("	date TEXT,	\n");
        sql.append("	stocksnumber TEXT,	\n");
        sql.append("	stocksname TEXT,	\n");
        sql.append("	share DOUBLE	\n");
        sql.append("	)	\n");
//        db.execSQL(sql.toString());
        
        sql.setLength(0);
        sql.append("	create table zzhyfl (	\n");
        sql.append("	id INTEGER PRIMARY KEY AUTOINCREMENT,	\n");
        sql.append("	number TEXT,	\n");
        sql.append("	name TEXT	\n");
        sql.append("	)	\n");
//        db.execSQL(sql.toString());
        
        sql.setLength(0);
        sql.append("	create table Stocksfl (	\n");
        sql.append("	id INTEGER PRIMARY KEY AUTOINCREMENT,	\n");
        sql.append("	number TEXT,	\n");
        sql.append("	name TEXT,	\n");
        sql.append("	level1 TEXT,	\n");
        sql.append("	level2 TEXT,	\n");
        sql.append("	level3 TEXT,	\n");
        sql.append("	level4 TEXT	\n");
        sql.append("	)	\n");
//        db.execSQL(sql.toString());
        
        String sqlIdx5 = "CREATE INDEX idx_FundStocks_fundNumber ON FundStocks(fundnumber)";
        db.execSQL(sqlIdx5.toString());
        
        String sqlIdx6 = "CREATE INDEX idx_FundStocks_stocksnumber ON FundStocks(stocksnumber)";
        db.execSQL(sqlIdx6.toString());
        
        String sqlIdx7 = "CREATE INDEX idx_zzhyfl_number ON zzhyfl(number)";
        db.execSQL(sqlIdx7.toString());
        
        String sqlIdx8 = "CREATE INDEX idx_Stocksfl_number ON Stocksfl(number)";
        db.execSQL(sqlIdx8.toString());
        
        String sqlIdx9 = "CREATE INDEX idx_Stocksfl_level1 ON Stocksfl(level1)";
        db.execSQL(sqlIdx9.toString());
        
        String sqlIdx10 = "CREATE INDEX idx_Stocksfl_level2 ON Stocksfl(level2)";
        db.execSQL(sqlIdx10.toString());
        
        String sqlIdx11 = "CREATE INDEX idx_Stocksfl_level3 ON Stocksfl(level3)";
        db.execSQL(sqlIdx11.toString());
        
        String sqlIdx12 = "CREATE INDEX idx_Stocksfl_level4 ON Stocksfl(level4)";
        db.execSQL(sqlIdx12.toString());
        
        String sqlIdx13 = "ALTER TABLE 'FundStocks' ADD  'date' TEXT";
        db.execSQL(sqlIdx13.toString());*/
		
		SQLiteDatabase db = DBManager.getDB();
		
		StringBuffer sql = new StringBuffer();
        sql.append("	create table indexStocks (	\n");
        sql.append("	id INTEGER PRIMARY KEY AUTOINCREMENT,	\n");
        sql.append("	stocksnumber TEXT,	\n");
        sql.append("	indexnumber TEXT	\n");
        sql.append("	)	\n");
        db.execSQL(sql.toString());
        
        String sqlIdx13 = "CREATE INDEX idx_indexStocks_stocksnumber ON indexStocks(stocksnumber)";
        db.execSQL(sqlIdx13.toString());
        
        String sqlIdx14 = "CREATE INDEX idx_indexStocks_indexnumber ON indexStocks(indexnumber)";
        db.execSQL(sqlIdx14.toString());
	}
	
	public static List<Map<String,Object>> getFundList(String indexNumber,String fundTypeNumber,String filterStr)
	{
		List<Map<String,Object>> list = new LinkedList<Map<String,Object>>();
		
		List<String> dateList = getLatestQuarterDateList(12);
		
		StringBuffer sql = new StringBuffer();
			
		sql.append("	    select 	\n");
		sql.append("	      fund.number, 	\n");
		sql.append("	      fund.name, 	\n");
		String qty = "";
		String share = "";
		for(int i=dateList.size()-1;i>=0;i--)
		{
			if(i==dateList.size()-1)
			{
				qty = "ifnull(t"+i+".qty,'0')";
				share = "ifnull(t"+i+".share,'0')";
			}else
			{
				qty = qty+"||'-'||ifnull(t"+i+".qty,'0')";
				share = share+"||'-'||ifnull(t"+i+".share,'0')";
			}
		}
		sql.append("	      "+qty+" qty, 	\n");
		sql.append("	      t0.share share, 	\n");
		sql.append("	      ifnull(stocks.s,'') stocks 	\n");
		sql.append("	    from Fund fund	\n");

		sql.append("	    left join 	\n");
		sql.append("	        ( 	\n");
		sql.append("	SELECT t.fundnumber,	\n");
		sql.append("	       GROUP_CONCAT( t.s )  s	\n");
		sql.append("	  FROM ( 	\n");
		sql.append("	    SELECT t.fundnumber,	\n");
		sql.append("	           t2.name || '(' || t.c || ')' s	\n");
		sql.append("	      FROM ( 	\n");
		sql.append("	            SELECT t.fundnumber,	\n");
		sql.append("	                   t1.indexnumber,	\n");
		sql.append("	                   count( * )  c	\n");
		sql.append("	              FROM FundStocks t	\n");
		sql.append("	                   INNER JOIN indexStocks t1	\n");
		//sql.append("	                           ON t.stocksnumber = t1.stocksnumber	\n");
		sql.append("	                           on (t.stocksnumber = t1.stocksnumber 	\n");
		sql.append("	                           and t1.startDate <= '"+dateList.get(0)+"' 	\n");
		sql.append("	                           and (t1.endDate is null or t1.endDate='' or t1.endDate >= '"+dateList.get(0)+"')) 	\n");
		sql.append("	             WHERE t.date = '"+dateList.get(0)+"'	\n");
		sql.append("	             GROUP BY t.fundnumber,	\n");
		sql.append("	                      t1.indexnumber 	\n");
		sql.append("	        ) 	\n");
		sql.append("	        t	\n");
		sql.append("	           INNER JOIN indexset t2	\n");
		sql.append("	                   ON t.indexnumber = t2.number	\n");
		sql.append("	     ORDER BY t.c DESC 	\n");
		sql.append("	) 	\n");
		sql.append("	t	\n");
		sql.append("	 GROUP BY t.fundnumber	\n");
		sql.append("	        ) stocks on fund.number=stocks.fundnumber	\n");

		for(int i=0;i<dateList.size();i++)
		{
			sql.append("	    left join 	\n");
			sql.append("	        ( 	\n");
			sql.append("	        select 	\n");
			sql.append("	          fundstocks.fundnumber, 	\n");
			sql.append("	          count(distinct fundstocks.stocksnumber) qty, 	\n");
			sql.append("	          sum(fundstocks.share) share 	\n");
			sql.append("	        from FundStocks fundstocks	\n");
			sql.append("	        inner join indexStocks indexStocks	\n");
			sql.append("	        on (fundstocks.stocksnumber = indexStocks.stocksnumber 	\n");
			sql.append("	           and indexStocks.startDate <= '"+dateList.get(i)+"' 	\n");
			sql.append("	           and (indexStocks.endDate is null or indexStocks.endDate='' or indexStocks.endDate >= '"+dateList.get(i)+"')) 	\n");
			sql.append("	        where indexStocks.indexnumber='"+indexNumber+"' 	\n");
			sql.append("	        and fundstocks.date='"+dateList.get(i)+"' 	\n");
			sql.append("	        group by fundstocks.fundnumber 	\n");
			sql.append("	        ) t"+i+" on fund.number=t"+i+".fundnumber	\n");
		}

		sql.append("	where 1=1	\n");
		if("1".equals(fundTypeNumber))//指数
		{
			sql.append("	and (fund.type like '%指数%' or fund.type like '%分级%' or fund.type like '%ETF%' or fund.type like '%联接%') \n");
		}else if ("2".equals(fundTypeNumber))//股票、混合
		{
			sql.append("	and (fund.type like '%股票%' or fund.type like '%混合%' or 	fund.type like '%保本%')	\n");
			sql.append("	and (fund.type not like '%指数%' and fund.type not like '%分级%' and fund.type not like '%ETF%' and fund.type not like '%联接%')	\n");
            sql.append("	and (fund.name not like '%指数%' and fund.name not like '%分级%' and fund.name not like '%ETF%' and fund.name not like '%联接%')	\n");
		}else if ("3".equals(fundTypeNumber))//债券
		{
			sql.append("	and fund.type like '%债券%'	\n");
		}
		if(!StringUtil.isNullOrEmpty(filterStr))
		{
			sql.append("	and fund.name like '%"+filterStr+"%'	\n");
		}
		sql.append("	and (t0.qty > 0 	\n");
		for(int i=dateList.size()-1;i>=1;i--)
		{
			sql.append("	or t"+i+".qty > 0 	\n");
		}
		sql.append("	) 	\n");
		sql.append("	order by t0.qty desc,t0.share desc,fund.pinyin	\n");

		SQLiteDatabase db = DBManager.getDB();

		Cursor c = db.rawQuery(sql.toString(), null);
		while (c.moveToNext())
		{
            String number = c.getString(c.getColumnIndex("number"));
            String name = c.getString(c.getColumnIndex("name"));
            String aqty = c.getString(c.getColumnIndex("qty"));
            double ashare = c.getDouble(c.getColumnIndex("share"));
			String stocks = c.getString(c.getColumnIndex("stocks"));
            
            Map<String, Object> map = new HashMap<String, Object>(); 
            map.put("number", number); 
            map.put("name", name); 
            map.put("qty", aqty==null?"":aqty); 
            map.put("share", new BigDecimal(ashare).setScale(2, RoundingMode.HALF_UP));
			if(StringUtil.isNullOrEmpty(stocks))
			{
				map.put("stocks", "");
			}else {
				map.put("stocks", stocks);
			}
            map.put("stocks", stocks);
			list.add(map);
		}
		c.close();
		
		return list;
	}
	
	public static Map<String,String> getTop3HYFL(String level)
	{
		Map<String,String> map1 = new HashMap<String,String>();
		List<String> dateList = getLatestQuarterDateList(12);
		if(dateList.size()==0)
		{
			return map1;
		}
		String alevel = (StringUtil.isNullOrEmpty(level)?"1":level);
		Map<String,List<String>> map = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();
		sql.append("	select * from 					\n");
		sql.append("	(					\n");
		sql.append("	select 					\n");
		sql.append("	    fundstocks.fundnumber,					\n");
		sql.append("	    zzhyfl.name zzhyflname,					\n");
		sql.append("	    count(*) qty,					\n");
		sql.append("	sum(fundstocks.share) share					\n");
		sql.append("	from FundStocks fundstocks					\n");
		sql.append("	inner join Stocksfl stocksfl on fundstocks.stocksnumber=stocksfl.number					\n");
		sql.append("	inner join zzhyfl zzhyfl on stocksfl.level"+alevel+"=zzhyfl.number					\n");
		sql.append("	where fundstocks.date='"+dateList.get(0)+"' 	\n");
		sql.append("	group by fundstocks.fundnumber,zzhyfl.name,fundstocks.date					\n");
		sql.append("	) t					\n");
		sql.append("	order by t.fundnumber,t.share desc					\n");
		
		SQLiteDatabase db = DBManager.getDB();

		Cursor c = db.rawQuery(sql.toString(), null);
		while (c.moveToNext())
		{
            String fundnumber = c.getString(c.getColumnIndex("fundnumber"));
            String zzhyflname = c.getString(c.getColumnIndex("zzhyflname"));
            int qty = c.getInt(c.getColumnIndex("qty"));
            double share = c.getDouble(c.getColumnIndex("share"));
            
            List<String> list = map.get(fundnumber);
            if(list==null)
            {
            	list = new LinkedList<String>();
            	map.put(fundnumber, list);
            }
            if(list.size()>=3)
            {
            	continue;
            }
            list.add(zzhyflname+"("+qty+","+new BigDecimal(share).setScale(0, RoundingMode.HALF_UP)+"%)");
		}
		c.close();
		
		for(String key:map.keySet())
		{
			List<String> list = map.get(key);
			String str = null;
			for(int i=0;i<list.size();i++)
			{
				if(i==0)
				{
					str = list.get(i);
				}else
				{
					str = str + "," + list.get(i);
				}
			}
			map1.put(key, str);
		}
		
		return map1;
	}
	
	
	/**
	 * 查询行业分类
	 * @param
	 * @param parentNumber
	 * @param level
	 * @return
	 */
	public static List<String> getZZHYFLList(String parentNumber,String level)
	{
		List<String> list = new LinkedList<String>();
		StringBuffer sql = new StringBuffer();
		sql.append("	select 	\n");
		sql.append("	  number, 	\n");
		sql.append("	  name 	\n");
		sql.append("	from zzhyfl 	\n");
		sql.append("	where 1=1 	\n");
		if(!StringUtil.isNullOrEmpty(parentNumber))
		{
			sql.append("	and number like '"+parentNumber+"%' 	\n");
		}
		if(!StringUtil.isNullOrEmpty(level))
		{
			sql.append("	and length(number)="+Integer.valueOf(level)*2+" 	\n");
		}
		sql.append("	order by number 	\n");
		
		SQLiteDatabase db = DBManager.getDB();

		Cursor c = db.rawQuery(sql.toString(), null);
		while (c.moveToNext())
		{
            String number = c.getString(c.getColumnIndex("number"));
            String name = c.getString(c.getColumnIndex("name"));
            
            list.add(number+"-"+name);
		}
		c.close();
		
		return list;
	}
	
	/**
	 * 根据行业分类查询股票
	 * @param
	 * @param levelNumber
	 * @param level
	 * @return
	 */
	public static List<String> getStocksList(String levelNumber,String level,String s)
	{
		List<String> list = new LinkedList<String>();
		StringBuffer sql = new StringBuffer();
		sql.append("	select 	\n");
		sql.append("	  number, 	\n");
		sql.append("	  name 	\n");
		sql.append("	from Stocksfl 	\n");
		sql.append("	where 1=1 	\n");
		if(!StringUtil.isNullOrEmpty(levelNumber) && !StringUtil.isNullOrEmpty(level))
		{
			sql.append("	and level"+level+" = '"+levelNumber+"' 	\n");
		}
		sql.append("	order by number 	\n");
		
		SQLiteDatabase db = DBManager.getDB();

		Cursor c = db.rawQuery(sql.toString(), null);
		while (c.moveToNext())
		{
            String number = c.getString(c.getColumnIndex("number"));
            String name = c.getString(c.getColumnIndex("name"));
            
            list.add(number+"-"+name);
		}
		c.close();
		
		return list;
	}
	
	/**
	 * 获取基金10大持仓股票分类
	 * @param
	 * @param fundNumber
	 * @return
	 */
	public static List<Map<String,Object>> getFundStocksList(String fundNumber)
	{
		List<Map<String,Object>> list = new LinkedList<Map<String,Object>>();
		List<String> dateList = getLatestQuarterDateList(8);
		if(dateList.size()==0)
		{
			return list;
		}
		StringBuffer sql = new StringBuffer();
		sql.append("	select 				\n");
		sql.append("		fundstocks.stocksnumber,			\n");
		sql.append("		fundstocks.date,			\n");
		sql.append("		fundstocks.stocksname,			\n");
		sql.append("		fundstocks.share,			\n");
		sql.append("		zzhyfl1.name level1name,			\n");
		sql.append("		zzhyfl2.name level2name,			\n");
		sql.append("		zzhyfl3.name level3name,			\n");
		sql.append("		zzhyfl4.name level4name			\n");
		sql.append("	from FundStocks fundstocks				\n");
		sql.append("	left join Stocksfl stocksfl on fundstocks.stocksnumber=stocksfl.number				\n");
		sql.append("	left join zzhyfl zzhyfl1 on stocksfl.level1=zzhyfl1.number				\n");
		sql.append("	left join zzhyfl zzhyfl2 on stocksfl.level2=zzhyfl2.number				\n");
		sql.append("	left join zzhyfl zzhyfl3 on stocksfl.level3=zzhyfl3.number				\n");
		sql.append("	left join zzhyfl zzhyfl4 on stocksfl.level4=zzhyfl4.number				\n");
		sql.append("	where fundstocks.fundnumber='"+fundNumber+"'				\n");
		sql.append("	and fundstocks.date='"+dateList.get(0)+"' 	\n");
		sql.append("	order by fundstocks.share desc,stocksfl.level4				\n");
		
		SQLiteDatabase db = DBManager.getDB();

		Cursor c = db.rawQuery(sql.toString(), null);
		while (c.moveToNext())
		{
            String number = c.getString(c.getColumnIndex("stocksnumber"));
            String date = c.getString(c.getColumnIndex("date"));
            String name = c.getString(c.getColumnIndex("stocksname"));
            double share = c.getDouble(c.getColumnIndex("share"));
            String level1 = c.getString(c.getColumnIndex("level1name"));
            String level2 = c.getString(c.getColumnIndex("level2name"));
            String level3 = c.getString(c.getColumnIndex("level3name"));
            String level4 = c.getString(c.getColumnIndex("level4name"));
            
            Map<String,Object> map = new HashMap<String,Object>();
            map.put("stocks", StringUtil.dealNull(number)+" "+StringUtil.dealNull(name)+" "+StringUtil.dealNull(share));
            map.put("zzhyfl", StringUtil.dealNull(level1)+"-"+StringUtil.dealNull(level2)+"-"+StringUtil.dealNull(level3)+"-"+StringUtil.dealNull(level4));
            map.put("share", share);
            map.put("date", date);
            
            list.add(map);
		}
		c.close();
		
		return list;
	}

	/**
	 * 获取指数
	 * @return
	 */
	public static List<String> getIndexList()
	{
		List<String> list = new LinkedList<String>();
		String sql = "select number,name from indexSet order by seq";
		
		SQLiteDatabase db = DBManager.getDB();

		Cursor c = db.rawQuery(sql.toString(), null);
		while (c.moveToNext())
		{
            String number = c.getString(c.getColumnIndex("number"));
            String name = c.getString(c.getColumnIndex("name"));
            
            list.add(number+"-"+name);
		}
		c.close();
		
		return list;
	}
	
	/**
	 * 获取指数
	 * @return
	 */
	public static List<String> getIndexNumberList()
	{
		List<String> list = new LinkedList<String>();
		String sql = "select number from indexSet order by seq";
		
		SQLiteDatabase db = DBManager.getDB();

		Cursor c = db.rawQuery(sql.toString(), null);
		while (c.moveToNext())
		{
            String number = c.getString(c.getColumnIndex("number"));
            
            list.add(number);
		}
		c.close();
		
		return list;
	}
	
	/**
	 * 获取指数
	 * @return
	 */
	public static List<String> getLatestQuarterDateList(int n)
	{
		List<String> list = new LinkedList<String>();
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		Calendar c = Calendar.getInstance();
		String maxDate = getFundStocksMaxDate();
		if(!StringUtil.isNullOrEmpty(maxDate))
		{
			try {
				c.setTime(df.parse(maxDate));
				c.add(Calendar.DAY_OF_MONTH, 1);
			} catch (ParseException e) {
				e.printStackTrace();
			}
		}
/*		int month = c.get(Calendar.MONTH);
		Calendar startC = Calendar.getInstance();
		startC.set(Calendar.DAY_OF_MONTH, 1);
		if(month>=0 && month<=2)
		{
			startC.set(Calendar.MONTH, 0);
		}else if(month>=3 && month<=5)
		{
			startC.set(Calendar.MONTH, 3);
		}else if(month>=6 && month<=8)
		{
			startC.set(Calendar.MONTH, 6);
		}else if(month>=9 && month<=11)
		{
			startC.set(Calendar.MONTH, 9);
		}*/
/*		Calendar tc = Calendar.getInstance();
		tc.setTime(c.getTime());
		tc.add(Calendar.DAY_OF_MONTH, -1);*/
		list.add(maxDate);
		Calendar tc = Calendar.getInstance();
		for(int i=1;i<n;i++)
		{
			c.add(Calendar.MONTH, -3);
			tc.setTime(c.getTime());
			tc.add(Calendar.DAY_OF_MONTH, -1);
			list.add(df.format(tc.getTime()));
		}
		
		return list;
	}
	
	public static String getFundStocksMaxDate()
	{
		SQLiteDatabase db = DBManager.getDB();

		String lastDate = "";
		
		String sqlQuery = "select max(date) lastDate from FundStocks";
		Cursor c = db.rawQuery(sqlQuery, null);
		if (c.moveToNext())
		{
			String date = c.getString(c.getColumnIndex("lastDate"));
			if(date!=null)
			{
				lastDate = date;
			}
		}
		c.close();
		return lastDate;
	}
	
	public static FundStageRise getFundStageRise(String number,String refNumber,boolean isCheckUpdate)
	{
		FundStageRise info = new FundStageRise(number);
		
		UpdateStrategy updateStrategy = new FundNetValueUpdateStrategy(number);
		if(updateStrategy.isNeedUpdate() && isCheckUpdate)
		{
			updateStrategy.updateIndex();
		}
		
		List<String> dateList = FundUtil.getSHIndexDateList();
		if(dateList.size()==0)
		{
			return info;
		}
		
		FundInfo refInfo = FundUtil.getFundNetValueFromLocalhost("",refNumber);
		
		Date date0 = new Date();
		//最新时间
		String lastDate = dateList.get(0);
		try {
			date0 = DF1.parse(lastDate);
		} catch (ParseException e) {
			e.printStackTrace();
		}
		Calendar c = Calendar.getInstance();
		c.setTime(date0);
		//1日
		String day1 = dateList.get(1);
		//近1周
		String tWeek1 = null;
		c.add(Calendar.WEEK_OF_MONTH, -1);
		String week1 = DF1.format(c.getTime());
		//近1月
		String tMonth1 = null;
		c.setTime(date0);
		c.add(Calendar.MONTH, -1);
		String month1 = DF1.format(c.getTime());
		//近3月
		String tMonth3 = null;
		c.setTime(date0);
		c.add(Calendar.MONTH, -3);
		String month3 = DF1.format(c.getTime());
		//近6月
		String tMonth6 = null;
		c.setTime(date0);
		c.add(Calendar.MONTH, -6);
		String month6 = DF1.format(c.getTime());
		//近1年
		String tYear1 = null;
		c.setTime(date0);
		c.add(Calendar.YEAR, -1);
		String year1 = DF1.format(c.getTime());
		//近2年
		String tYear2 = null;
		c.setTime(date0);
		c.add(Calendar.YEAR, -2);
		String year2 = DF1.format(c.getTime());
		
		IAmplitude iAmplitude = new Amplitude();
		List<String> amplitudeDateList = iAmplitude.getAmplitudeDateList(refInfo, dateList, 10.00d, -9.00d);
		int s = amplitudeDateList.size();
		String tQ = s>=2?amplitudeDateList.get(s-2):"";
		String tQ1 = s>=3?amplitudeDateList.get(s-3):"";
		String tQ2 = s>=4?amplitudeDateList.get(s-4):"";
		String tQ3 = s>=5?amplitudeDateList.get(s-5):"";
		String tQ4 = s>=6?amplitudeDateList.get(s-6):"";
		String tQ5 = s>=7?amplitudeDateList.get(s-7):"";
		String tQ6 = s>=8?amplitudeDateList.get(s-8):"";
		String tQ7 = s>=9?amplitudeDateList.get(s-9):"";
		String tQ8 = s>=10?amplitudeDateList.get(s-10):"";
		String tQ9 = s>=11?amplitudeDateList.get(s-11):"";
		String tQ10 = s>=12?amplitudeDateList.get(s-12):"";
		
		for(int i=0;i<dateList.size();i++)
		{
			if(tWeek1!=null && tMonth1!=null && tMonth3!=null
					&& tMonth6!=null && tYear1!=null && tYear2!=null
				)
			{
				break;
			}
			
			String date = dateList.get(i);
			if(tWeek1==null && date.compareTo(week1)<=0)
			{
				tWeek1 = date;
			}else if(tMonth1==null && date.compareTo(month1)<=0)
			{
				tMonth1 = date;
			}
			else if(tMonth3==null && date.compareTo(month3)<=0)
			{
				tMonth3 = date;
			}
			else if(tMonth6==null && date.compareTo(month6)<=0)
			{
				tMonth6 = date;
			}
			else if(tYear1==null && date.compareTo(year1)<=0)
			{
				tYear1 = date;
			}
			else if(tYear2==null && date.compareTo(year2)<=0)
			{
				tYear2 = date;
			}
		}
		
		StringBuffer sql = new StringBuffer();
		sql.append("	SELECT t.fundNumber, t.netValue AS netValue, t1.netValue AS netValue1, t2.netValue AS netValue2, t3.netValue AS netValue3				\n");
		sql.append("		, t4.netValue AS netValue4, t5.netValue AS netValue5, t6.netValue AS netValue6, t7.netValue AS netValue7			\n");
		sql.append("		, tQ.netValue AS netValueTQ, tQ1.netValue AS netValueTQ1, tQ2.netValue AS netValueTQ2, tQ3.netValue AS netValueTQ3			\n");
		sql.append("		, tQ4.netValue AS netValueTQ4, tQ5.netValue AS netValueTQ5, tQ6.netValue AS netValueTQ6, tQ7.netValue AS netValueTQ7, tQ8.netValue AS netValueTQ8			\n");
		sql.append("		, tQ9.netValue AS netValueTQ9, tQ10.netValue AS netValueTQ10			\n");
		sql.append("	FROM (				\n");
		sql.append("		SELECT fundNumber, netValue			\n");
		sql.append("		FROM FundNetValue			\n");
		sql.append("		WHERE fundNumber = '"+number+"'			\n");
		sql.append("			AND date = '"+lastDate+"'		\n");
		sql.append("	) t				\n");
		sql.append("		LEFT JOIN (			\n");
		sql.append("			SELECT fundNumber, netValue		\n");
		sql.append("			FROM FundNetValue		\n");
		sql.append("			WHERE fundNumber = '"+number+"'		\n");
		sql.append("				AND date = '"+day1+"'	\n");
		sql.append("		) t1			\n");
		sql.append("		ON t.fundNumber = t1.fundNumber			\n");
		sql.append("		LEFT JOIN (			\n");
		sql.append("			SELECT fundNumber, netValue		\n");
		sql.append("			FROM FundNetValue		\n");
		sql.append("			WHERE fundNumber = '"+number+"'		\n");
		sql.append("				AND date = '"+tWeek1+"'	\n");
		sql.append("		) t2			\n");
		sql.append("		ON t.fundNumber = t2.fundNumber			\n");
		sql.append("		LEFT JOIN (			\n");
		sql.append("			SELECT fundNumber, netValue		\n");
		sql.append("			FROM FundNetValue		\n");
		sql.append("			WHERE fundNumber = '"+number+"'		\n");
		sql.append("				AND date = '"+tMonth1+"'	\n");
		sql.append("		) t3			\n");
		sql.append("		ON t.fundNumber = t3.fundNumber			\n");
		sql.append("		LEFT JOIN (			\n");
		sql.append("			SELECT fundNumber, netValue		\n");
		sql.append("			FROM FundNetValue		\n");
		sql.append("			WHERE fundNumber = '"+number+"'		\n");
		sql.append("				AND date = '"+tMonth3+"'	\n");
		sql.append("		) t4			\n");
		sql.append("		ON t.fundNumber = t4.fundNumber			\n");
		sql.append("		LEFT JOIN (			\n");
		sql.append("			SELECT fundNumber, netValue		\n");
		sql.append("			FROM FundNetValue		\n");
		sql.append("			WHERE fundNumber = '"+number+"'		\n");
		sql.append("				AND date = '"+tMonth6+"'	\n");
		sql.append("		) t5			\n");
		sql.append("		ON t.fundNumber = t5.fundNumber			\n");
		sql.append("		LEFT JOIN (			\n");
		sql.append("			SELECT fundNumber, netValue		\n");
		sql.append("			FROM FundNetValue		\n");
		sql.append("			WHERE fundNumber = '"+number+"'		\n");
		sql.append("				AND date = '"+tYear1+"'	\n");
		sql.append("		) t6			\n");
		sql.append("		ON t.fundNumber = t6.fundNumber			\n");
		sql.append("		LEFT JOIN (			\n");
		sql.append("			SELECT fundNumber, netValue		\n");
		sql.append("			FROM FundNetValue		\n");
		sql.append("			WHERE fundNumber = '"+number+"'		\n");
		sql.append("				AND date = '"+tYear2+"'	\n");
		sql.append("		) t7			\n");
		sql.append("		ON t.fundNumber = t7.fundNumber			\n");
		sql.append("		LEFT JOIN (			\n");
		sql.append("			SELECT fundNumber, netValue		\n");
		sql.append("			FROM FundNetValue		\n");
		sql.append("			WHERE fundNumber = '"+number+"'		\n");
		sql.append("				AND date = '"+tQ+"'	\n");
		sql.append("		) tQ			\n");
		sql.append("		ON t.fundNumber = tQ.fundNumber			\n");
		sql.append("		LEFT JOIN (			\n");
		sql.append("			SELECT fundNumber, netValue		\n");
		sql.append("			FROM FundNetValue		\n");
		sql.append("			WHERE fundNumber = '"+number+"'		\n");
		sql.append("				AND date = '"+tQ1+"'	\n");
		sql.append("		) tQ1			\n");
		sql.append("		ON t.fundNumber = tQ1.fundNumber			\n");
		sql.append("		LEFT JOIN (			\n");
		sql.append("			SELECT fundNumber, netValue		\n");
		sql.append("			FROM FundNetValue		\n");
		sql.append("			WHERE fundNumber = '"+number+"'		\n");
		sql.append("				AND date = '"+tQ2+"'	\n");
		sql.append("		) tQ2			\n");
		sql.append("		ON t.fundNumber = tQ2.fundNumber			\n");
		sql.append("		LEFT JOIN (			\n");
		sql.append("			SELECT fundNumber, netValue		\n");
		sql.append("			FROM FundNetValue		\n");
		sql.append("			WHERE fundNumber = '"+number+"'		\n");
		sql.append("				AND date = '"+tQ3+"'	\n");
		sql.append("		) tQ3			\n");
		sql.append("		ON t.fundNumber = tQ3.fundNumber			\n");
		sql.append("		LEFT JOIN (			\n");
		sql.append("			SELECT fundNumber, netValue		\n");
		sql.append("			FROM FundNetValue		\n");
		sql.append("			WHERE fundNumber = '"+number+"'		\n");
		sql.append("				AND date = '"+tQ4+"'	\n");
		sql.append("		) tQ4			\n");
		sql.append("		ON t.fundNumber = tQ4.fundNumber			\n");
		sql.append("		LEFT JOIN (			\n");
		sql.append("			SELECT fundNumber, netValue		\n");
		sql.append("			FROM FundNetValue		\n");
		sql.append("			WHERE fundNumber = '"+number+"'		\n");
		sql.append("				AND date = '"+tQ5+"'	\n");
		sql.append("		) tQ5			\n");
		sql.append("		ON t.fundNumber = tQ5.fundNumber			\n");
		sql.append("		LEFT JOIN (			\n");
		sql.append("			SELECT fundNumber, netValue		\n");
		sql.append("			FROM FundNetValue		\n");
		sql.append("			WHERE fundNumber = '"+number+"'		\n");
		sql.append("				AND date = '"+tQ6+"'	\n");
		sql.append("		) tQ6			\n");
		sql.append("		ON t.fundNumber = tQ6.fundNumber			\n");
		sql.append("		LEFT JOIN (			\n");
		sql.append("			SELECT fundNumber, netValue		\n");
		sql.append("			FROM FundNetValue		\n");
		sql.append("			WHERE fundNumber = '"+number+"'		\n");
		sql.append("				AND date = '"+tQ7+"'	\n");
		sql.append("		) tQ7			\n");
		sql.append("		ON t.fundNumber = tQ7.fundNumber			\n");
		sql.append("		LEFT JOIN (			\n");
		sql.append("			SELECT fundNumber, netValue		\n");
		sql.append("			FROM FundNetValue		\n");
		sql.append("			WHERE fundNumber = '"+number+"'		\n");
		sql.append("				AND date = '"+tQ8+"'	\n");
		sql.append("		) tQ8			\n");
		sql.append("		ON t.fundNumber = tQ8.fundNumber			\n");
		sql.append("		LEFT JOIN (			\n");
		sql.append("			SELECT fundNumber, netValue		\n");
		sql.append("			FROM FundNetValue		\n");
		sql.append("			WHERE fundNumber = '"+number+"'		\n");
		sql.append("				AND date = '"+tQ9+"'	\n");
		sql.append("		) tQ9			\n");
		sql.append("		ON t.fundNumber = tQ9.fundNumber			\n");
		sql.append("		LEFT JOIN (			\n");
		sql.append("			SELECT fundNumber, netValue		\n");
		sql.append("			FROM FundNetValue		\n");
		sql.append("			WHERE fundNumber = '"+number+"'		\n");
		sql.append("				AND date = '"+tQ10+"'	\n");
		sql.append("		) tQ10			\n");
		sql.append("		ON t.fundNumber = tQ10.fundNumber			\n");

		Cursor cs = null;
		try {
			SQLiteDatabase db = DBManager.getDB();
			
			cs = db.rawQuery(sql.toString(), null);
			if (cs.moveToNext())
			{
				String netValue = cs.getString(cs.getColumnIndex("netValue"));
				String netValue1 = cs.getString(cs.getColumnIndex("netValue1"));
				String netValue2 = cs.getString(cs.getColumnIndex("netValue2"));
				String netValue3 = cs.getString(cs.getColumnIndex("netValue3"));
				String netValue4 = cs.getString(cs.getColumnIndex("netValue4"));
				String netValue5 = cs.getString(cs.getColumnIndex("netValue5"));
				String netValue6 = cs.getString(cs.getColumnIndex("netValue6"));
				String netValue7 = cs.getString(cs.getColumnIndex("netValue7"));
				String netValueTQ = cs.getString(cs.getColumnIndex("netValueTQ"));
				String netValueTQ1 = cs.getString(cs.getColumnIndex("netValueTQ1"));
				String netValueTQ2 = cs.getString(cs.getColumnIndex("netValueTQ2"));
				String netValueTQ3 = cs.getString(cs.getColumnIndex("netValueTQ3"));
				String netValueTQ4 = cs.getString(cs.getColumnIndex("netValueTQ4"));
				String netValueTQ5 = cs.getString(cs.getColumnIndex("netValueTQ5"));
				String netValueTQ6 = cs.getString(cs.getColumnIndex("netValueTQ6"));
				String netValueTQ7 = cs.getString(cs.getColumnIndex("netValueTQ7"));
				String netValueTQ8 = cs.getString(cs.getColumnIndex("netValueTQ8"));
				String netValueTQ9 = cs.getString(cs.getColumnIndex("netValueTQ9"));
				String netValueTQ10 = cs.getString(cs.getColumnIndex("netValueTQ10"));
	            
				if(netValue==null)
				{
					return info;
				}
				info.setDayRise(calRise(netValue,netValue1));
				info.setWeekRise(calRise(netValue,netValue2));
				info.setMonthRise(calRise(netValue,netValue3));
				info.setThreeMonthRise(calRise(netValue,netValue4));
				info.setSixMonthRise(calRise(netValue,netValue5));
				info.setYearRise(calRise(netValue,netValue6));
				info.setTwoYearRise(calRise(netValue,netValue7));
				info.setQ(calRise(netValue,netValueTQ));
				info.setQ1(calRise(netValueTQ,netValueTQ1));
				info.setQ2(calRise(netValueTQ1,netValueTQ2));
				info.setQ3(calRise(netValueTQ2,netValueTQ3));
				info.setQ4(calRise(netValueTQ3,netValueTQ4));
				info.setQ5(calRise(netValueTQ4,netValueTQ5));
				info.setQ6(calRise(netValueTQ5,netValueTQ6));
				info.setQ7(calRise(netValueTQ6,netValueTQ7));
				info.setQ8(calRise(netValueTQ7,netValueTQ8));
				info.setQ9(calRise(netValueTQ8,netValueTQ9));
				info.setQ10(calRise(netValueTQ9,netValueTQ10));
/*				if(StringUtil.isNullOrEmpty(info.getQ()))
				{
					if(Double.parseDouble(info.getQ())>=10)
					{
						info.setQ9(calRise(netValueTQ,netValueTQ1));
						info.setQ10(calRise(netValueTQ1,netValueTQ2));
						info.setQ11(calRise(netValueTQ2,netValueTQ3));
						info.setQ12(calRise(netValueTQ3,netValueTQ4));
					}else
					{
						info.setQ1(calRise(netValueTQ,netValueTQ1));
						info.setQ2(calRise(netValueTQ1,netValueTQ2));
						info.setQ3(calRise(netValueTQ2,netValueTQ3));
						info.setQ4(calRise(netValueTQ3,netValueTQ4));
					}
				}*/
			}	
		} catch (Exception e) {
			e.printStackTrace();
		}finally
		{
			if(cs!=null)
			{
				cs.close();
			}
		}

		
		return info;
	}
	
	public static String calRise(String v1,String v2)
	{
		String vStr = null;
		if(!StringUtil.isNullOrEmpty(v1) && !StringUtil.isNullOrEmpty(v2) && new BigDecimal(v2).compareTo(BigDecimal.ZERO)>0)
		{
			BigDecimal v = new BigDecimal((Double.valueOf(v1)-Double.valueOf(v2))*100.00d/Double.valueOf(v2));
			vStr = v.setScale(2,RoundingMode.HALF_UP).toString();
		}
		
		return vStr;
	}
	
	public static void test()
	{
/*		for(int i=0;i<Constant.indexNumbers.length;i++)
		{
			List<Entry<String,Double>> mappingList = FundUtil1.getCorrel(db,Constant.indexNumbers[i][0]);
			String text = "";
			int j=0;
			for(Map.Entry<String,Double> mapping:mappingList)
			{ 
				if(j==0)
				{
					text = Constant.indexNamesMap.get(mapping.getKey())+"("+mapping.getValue()+")";
				}else
				{
					text = text+" "+Constant.indexNamesMap.get(mapping.getKey())+"("+mapping.getValue()+")";
					
				}
				j++;
			} 
		}*/
/*		String testSql1 = "select fundnumber,stocksnumber,stocksname,share from fundstocks order by fundnumber";
		Cursor c1 = db.rawQuery(testSql1.toString(), null);
		int i=0;
		while (c1.moveToNext())
		{
            String fundnumber = c1.getString(c1.getColumnIndex("fundnumber"));
            String stocksnumber = c1.getString(c1.getColumnIndex("stocksnumber"));
            String stocksname = c1.getString(c1.getColumnIndex("stocksname"));
            String share = c1.getString(c1.getColumnIndex("share"));
            i++;
		}
		c1.close();
		
		String testSql2 = "select number,name,level1 from Stocksfl order by number";
		Cursor c2 = db.rawQuery(testSql2.toString(), null);
		int j=0;
		while (c2.moveToNext())
		{
            String number = c2.getString(c2.getColumnIndex("number"));
            String name = c2.getString(c2.getColumnIndex("name"));
            String level1 = c2.getString(c2.getColumnIndex("level1"));
            j++;
		}
		c2.close();*/
	}
}
